package com.fable.sp.core.service.impl;

import com.fable.sp.core.domain.ColumnDef;
import com.fable.sp.core.domain.ContainerTable;
import com.fable.sp.core.repository.ContainerTableRepository;
import com.fable.sp.core.service.TableNameResolver;
import com.fable.sp.core.util.dbutilsjpa.JpaQueryRunner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import freemarker.template.TemplateException;
import groovy.lang.GroovyClassLoader;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;
import org.springframework.util.ClassUtils;
import org.springframework.util.ReflectionUtils;
import org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

/**
 * .
 *
 * @author stormning on 16/7/30.
 */
@Service
@Transactional(value = "spTxManager", readOnly = true)
public class TableBuilder implements InitializingBean {

    public static final ScalarHandler<Long> DEFAULT_HANDLER = new ScalarHandler<Long>();
    private static final Logger LOGGER = LoggerFactory.getLogger(TableBuilder.class);
    @Autowired
    private FreeMarkerConfigurer configurer;
    @Autowired
    private ContainerTableRepository containerTableRepository;
    @Autowired
    private DataSource dataSource;
    private Map<Long, Class> containerTableClasses = Maps.newConcurrentMap();
    private GroovyClassLoader classLoader = new GroovyClassLoader(ClassUtils.getDefaultClassLoader());
    private TableNameResolver tableNameResolver = new ContainerTableNameResolver();
    /**
     * hibernate configuration
     */
    private JpaQueryRunner jpaQueryRunner;
    private QueryRunner queryRunner;

    public Object getColumnValue(Long containerId, Object record, String columnName) {
        if (record == null) {
            return null;
        }
        try {
            Class aClass = containerTableClasses.get(containerId);
            Field field = ReflectionUtils.findField(aClass, columnName);
            ReflectionUtils.makeAccessible(field);
            return field.get(record);
        } catch (Exception e) {
            LOGGER.error("get column value error", e);
            return null;
        }
    }

    public Object getRecord(Long containerId, Long recordId) {
        return jpaQueryRunner.query(containerTableClasses.get(containerId), recordId);
    }

    private Platform getPlatform(String tableName) {
        Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
        platform.getModelReader().setDefaultTablePattern(tableName + "%");
        return platform;
    }

    private Database getDatabase(ContainerTable containerTable) {
        Database database = new Database();
        Table table = new Table();
        table.setName(containerTable.getTableName());

        Column key = new Column();
        key.setName("id");
        key.setTypeCode(Types.BIGINT);
        key.setPrimaryKey(true);

        table.addColumn(key);
        for (ColumnDef columnDef : containerTable.getInstanceColumns().values()) {
            Column column = new Column();
            int length = columnDef.getLength();
            switch (columnDef.getColumnType()) {
                case STRING:
                    if (length > 2000) {
                        column.setTypeCode(Types.CLOB);
                    } else {
                        column.setTypeCode(Types.VARCHAR);
                        column.setSize(String.valueOf(length));
                    }
                    break;
                case NUMBER:
                    column.setTypeCode(Types.BIGINT);
                    column.setSize(String.valueOf(20));
                    break;
                case LOB:
                    column.setTypeCode(Types.CLOB);
                    break;
            }
            column.setName(columnDef.getRealColumnName());
            table.addColumn(column);
        }
        database.addTable(table);
        return database;
    }

    private void setupContainerTableClasses(ContainerTable containerTable) throws IOException, TemplateException {
        Map<String, Object> model = Maps.newHashMap();
        model.put("containerTable", containerTable);
        String classString = FreeMarkerTemplateUtils.processTemplateIntoString(configurer.getConfiguration().getTemplate("repo.ftl"), model);
        //add class to entity manager
        ClassUtils.overrideThreadContextClassLoader(classLoader);
        Class entityClass = classLoader.parseClass(classString);
        //table entityClass map
        containerTableClasses.put(containerTable.getContainerId(), entityClass);
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        this.queryRunner = new QueryRunner(dataSource);
        this.jpaQueryRunner = new JpaQueryRunner.Builder().build(queryRunner);
        List<ContainerTable> tables = containerTableRepository.findAll();
        for (ContainerTable containerTable : tables) {
            setupContainerTableClasses(containerTable);
        }
    }

    @Transactional("spTxManager")
    public Object newRecord(long containerId) {
        Class aClass = containerTableClasses.get(containerId);
        Object ins = BeanUtils.instantiate(aClass);
        saveOrUpdateRecord(ins);
        return ins;
    }

    @Transactional("spTxManager")
    public Long saveOrUpdateRecord(Object data) {
        jpaQueryRunner.save(data);
        return (Long) com.fable.sp.core.util.BeanUtils.getProperty(data, "id");
    }

    @Transactional("spTxManager")
    @SuppressWarnings("unchecked")
    public ContainerTable saveContainerTable(Long containerId, Map<Long, ColumnDef> newInstanceColumns) throws Exception {
        ContainerTable containerTable = containerTableRepository.findOne(containerId);
        String tableName = tableNameResolver.generateTableName(containerId);
        Platform platform = getPlatform(tableName);
        try {

            if (containerTable == null) {
                containerTable = new ContainerTable();
                containerTable.setTableName(tableName);
                containerTable.setContainerId(containerId);
                containerTable.setInstanceColumns(newInstanceColumns);
                platform.createTables(getDatabase(containerTable), false, true);
            } else {
                Map<Long, ColumnDef> oldInstanceColumns = containerTable.getInstanceColumns();
                //check if has record
                CompareResult compareResult = compare(oldInstanceColumns, newInstanceColumns);
                if (!compareResult.same) {
                    containerTable.setInstanceColumns(newInstanceColumns);
                    if (hasAnyRecord(tableName)) {
                        String backupName = null;
                        try {
                            //backup table
                            backupName = backupTable(containerTable.getTableName());
                            //create new table
                            platform.createTables(getDatabase(containerTable), false, true);
                            //copy records
                            copyRecords(newInstanceColumns, tableName, oldInstanceColumns, backupName);
                        } catch (Exception e) {
                            //if backup and copy failed revert
                            if (backupName != null) {
                                //drop new table
                                dropTable(tableName);
                                //rename backupName to tableName
                                changeTableName(backupName, tableName);
                                //revert columns
                                containerTable.setInstanceColumns(oldInstanceColumns);
                            }
                            throw e;
                        }
                    } else {
                        platform.alterTables(getDatabase(containerTable), true);
                    }
                }
            }
        } finally {
            platform.shutdownDatabase();
        }

        setupContainerTableClasses(containerTable);
        return containerTable;
    }

    private void copyRecords(Map<Long, ColumnDef> newInstanceColumns, String tableName, Map<Long, ColumnDef> oldInstanceColumns, String backupName) throws SQLException {
        StringBuilder builder = new StringBuilder();
        builder.append("insert into ").append(tableName).append("(id,");
        int counter = 0;
        List<String> oldColumns = Lists.newArrayList();
        for (Map.Entry<Long, ColumnDef> entry : newInstanceColumns.entrySet()) {
            Long insId = entry.getKey();
            ColumnDef oldDef = oldInstanceColumns.get(insId);
            if (oldDef != null) {
                if (counter > 0) {
                    builder.append(",");
                }
                ColumnDef def = entry.getValue();
                builder.append(def.getRealColumnName());
                oldColumns.add(oldDef.getRealColumnName());
                counter++;
            }
        }
        builder.append(") select id,");
        if (counter > 0) {
            counter = 0;
            for (String oldColumn : oldColumns) {
                if (counter > 0) {
                    builder.append(",");
                }
                builder.append(oldColumn);
                counter++;
            }
            //do copy
            builder.append(" from ").append(backupName);
            queryRunner.update(builder.toString());
        }
    }

    public String backupTable(String tableName) throws SQLException {
        String backupName = tableName + "_" + DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMddhhss");
        changeTableName(tableName, backupName);
        return backupName;
    }

    public void changeTableName(String form, String to) throws SQLException {
        queryRunner.update("alter table " + form + " rename to " + to);
    }

    public void dropTable(String table) throws SQLException {
        queryRunner.update("drop table " + table);
    }

    private CompareResult compare(Map<Long, ColumnDef> oldInstanceColumns, Map<Long, ColumnDef> newInstanceColumns) {
        CompareResult result = new CompareResult();
        Map<Long, ColumnDef> newCopy = Maps.newHashMap(newInstanceColumns);

        for (Map.Entry<Long, ColumnDef> oldDef : oldInstanceColumns.entrySet()) {
            Long insId = oldDef.getKey();
            ColumnDef def = oldDef.getValue();
            ColumnDef newDef = newCopy.get(insId);
            if (newDef != null) {
                if (def.getColumnType() != newDef.getColumnType() || def.getLength() != newDef.getLength()) {
                    result.same = false;
                }
                if (!def.getRealColumnName().equals(newDef.getRealColumnName())) {
                    result.same = false;
                    result.renamedColumns.put(def.getRealColumnName(), newDef.getRealColumnName());
                }
                newCopy.remove(insId);
            } else {
                //remove column
                result.removeColumn(def.getRealColumnName());
            }
        }

        for (Map.Entry<Long, ColumnDef> newDef : newCopy.entrySet()) {
            result.same = false;
            result.newColumns.add(newDef.getValue().getRealColumnName());
        }
        return result;
    }

    private boolean hasAnyRecord(String tableName) {
        try {
            return queryRunner.query("select count(*) from " + tableName, DEFAULT_HANDLER) > 0;
        } catch (SQLException e) {
            return false;
        }
    }

    private class CompareResult {
        boolean same = true;
        List<String> removedColumns = Lists.newArrayList();
        //from->to
        Map<String, String> renamedColumns = Maps.newHashMap();
        List<String> newColumns = Lists.newArrayList();

        public void removeColumn(String columnName) {
            same = false;
            removedColumns.add(columnName);
        }
    }
}
